package com.navi.loaddata.loadService.impl.edaDifference;

import com.navi.loaddata.core.errorLog.ErrorLog;
import com.navi.loaddata.core.etlException.ETLException;
import com.navi.loaddata.core.gpEntity.GpChipF;
import com.navi.loaddata.core.gpEntity.GpDefectF;
import com.navi.loaddata.core.gpEntity.GpGlassF;
import com.navi.loaddata.core.gpEntity.MppLayoutPnlD;
import com.navi.loaddata.core.mysqlEntity.EdaDiffD;
import com.navi.loaddata.core.mysqlEntity.EdaDiffDModel;
import com.navi.loaddata.core.mysqlEntity.EtlConfDModel;
import com.navi.loaddata.core.oracleEntity.*;
import com.navi.loaddata.core.repository.impl.MySqlRepositoryImpl;
import com.navi.loaddata.core.repository.impl.OracleRepositoryImpl;
import com.navi.loaddata.core.util.JdbcUtil;
import com.navi.loaddata.core.util.LoggerUtil;
import com.navi.loaddata.loadDao.SaveBaseDataDao;
import com.navi.loaddata.loadDao.SaveFileDataDao;
import com.navi.loaddata.loadService.impl.baseData.LoadMesBaseDataService;
import com.navi.loaddata.loadService.impl.measureData.LoadDefectInfoService;
import com.navi.loaddata.loadService.impl.measureData.ProcessDefectDataService;
import org.apache.commons.collections.map.HashedMap;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author xufan
 */
@Service
//@Transactional(transactionManager = "primaryPlatformTransactionManager")
public class LoadEdaDiffService {
    private static final Logger logger = LoggerFactory.getLogger(LoadEdaDiffService.class);
    private LoggerUtil loggerUtil = new LoggerUtil(this.getClass());

    @Autowired
    private SaveEdaDiff saveEdaDiff;

    public String[] loadEdaDiffMain(EtlConfDModel etlConfD) throws Exception {
        String[] resMsg=new String[3];

//        Session session = dao3.getEntityManagerSession();
        try {
            resMsg = loadEdaDiff(etlConfD);
            String msg=etlConfD.getOpe();

            if(StringUtils.isEmpty(etlConfD.getOpe())) msg="ALL";
            logger.info("OpeId={} loadEdaDiffMain finish!",msg);
        } catch (ETLException e) {
            throw new ETLException(e.getMessage());
        }
        return resMsg;
    }

    /**
     * Load Measure Data
     * <p>
     * load base -> session table
     * load defect data  -> session table
     * call base proc  -> auto commit
     * call defect proc -> no auto commit
     * delete defect session table
     *
     * @param etlConfD
     */
    private String[] loadEdaDiff(EtlConfDModel etlConfD) throws Exception {
        String[] resMsg=new String[3];
        int okCnt = 0;
        int errCnt=0;
        String errMsg=null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Timestamp startTime=etlConfD.getRunStartTimestamp();
        Timestamp endTime=etlConfD.getRunEndTimestamp();
        String start=etlConfD.getRunStartTimestamp().toString().substring(0,19);
        String end=etlConfD.getRunEndTimestamp().toString().substring(0,19);;
        Timestamp updateTime = Timestamp.valueOf(LocalDateTime.now());

        QueryRunner qr_gp = JdbcUtil.getQueryRunner_gp();
        QueryRunner qr_ora = JdbcUtil.getQueryRunner_ora_edaldr();
        // gp  cnt
        String arrGpCntSql="select " +
                " shop_id as shop," +
                " ope_id as ope," +
                " count(0) as gpCnt " +
                " from wpp_adefect_glass_f " +
                " where EVT_timestamp>='"+start+"' and EVT_timestamp<'"+end+"' " +
                " and ope_id in('A7700','A7600','A1353','A2353','A3353','A4353','A5353')" +
                " and real_flg='Y'" +
                " and glass_id like 'A%'" +
                " and length(glass_id)>=10 " +
                " and EVT_timestamp<='"+end+"'" +
                " group by shop_id,OPE_ID" +
                " ORDER BY OPE_ID";
        String arrGpCntSqlALL="select " +
                " shop_id as shop," +
                " NULL as ope," +
                " count(0) as gpCnt " +
                " from wpp_adefect_glass_f " +
                " where EVT_timestamp>='"+start+"' and EVT_timestamp<'"+end+"' " +
                " and real_flg='Y'" +
                " and glass_id like 'A%'" +
                " and length(glass_id)>=10" +
                " and EVT_timestamp<='"+end+"'" +
                " group by shop_id";
        String cfGpCntSql="select " +
                " shop_id as shop," +
                " ope_id as ope," +
                " count(0) as gpCnt " +
                " from wpp_fdefect_glass_f " +
                " where EVT_timestamp>='"+start+"' and EVT_timestamp<'"+end+"' " +
                " and ope_id in('C7590','C7563','C759I')" +
                " and real_flg='Y'" +
                " and glass_id like 'C%'" +
                " and length(glass_id)>=10" +
                " and EVT_timestamp<='"+end+"'" +
                " group by shop_id,OPE_ID" +
                " ORDER BY OPE_ID";
        String cfGpCntSqlALL="select " +
                " shop_id as shop," +
                " NULL as ope," +
                " count(0) as gpCnt " +
                " from wpp_fdefect_glass_f " +
                " where EVT_timestamp>='"+start+"' and EVT_timestamp<'"+end+"' " +
                " and real_flg='Y'" +
                " and glass_id like 'C%'" +
                " and length(glass_id)>=10" +
                " and EVT_timestamp<='"+end+"'" +
                " group by shop_id";
        String cell1GpCntSql="select " +
                " shop_id as shop," +
                " ope_id as ope," +
                " count(0) as gpCnt " +
                " from wpp_cdefect_glass_f " +
                " where EVT_timestamp>='"+start+"' and EVT_timestamp<'"+end+"' " +
                " and real_flg='Y'" +
                " and (glass_id like 'A%' or glass_id like'C%')" +
                " and length(glass_id)>=10" +
                " and EVT_timestamp<='"+end+"'" +
                " group by shop_id,OPE_ID" +
                " ORDER BY OPE_ID";
        String cell2GpCntSql="select " +
                " shop_id as shop," +
                " ope_id as ope," +
                " count(0) as gpCnt " +
                " from wpp_cdefect_glass_f " +
                " where EVT_timestamp>='"+start+"' and EVT_timestamp<'"+end+"' " +
                " and ope_id in('4600','4610','7200','7210','4670','466X')" +
                " and real_flg='Y'" +
                " and glass_id like 'A%'" +
                " and length(glass_id)>=10" +
                " and EVT_timestamp<='"+end+"'" +
                " group by shop_id,OPE_ID" +
                " ORDER BY OPE_ID";

        List<EdaDiffDModel> arrGp = qr_gp.query(arrGpCntSql, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));
        List<EdaDiffDModel> cfGp = qr_gp.query(cfGpCntSql, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));
        List<EdaDiffDModel> cell1Gp = qr_gp.query(cell1GpCntSql, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));
        List<EdaDiffDModel> cell2Gp = qr_gp.query(cell2GpCntSql, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));

        List<EdaDiffDModel> arrGpALL = qr_gp.query(arrGpCntSqlALL, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));
        List<EdaDiffDModel> cfGpALL = qr_gp.query(cfGpCntSqlALL, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));

        //ora cnt
        String arrOraCntSql="SELECT " +
                "'ARRAY' AS SHOP," +
                " STEP_ID AS OPE," +
                " COUNT(STEP_ID) AS oraCnt " +
                " FROM ARRAY_GLASS_T " +
                " WHERE glass_start_time >=to_date('"+start+"','yyyy-mm-dd hh24:mi:ss')" +
                " AND glass_start_time <to_date('"+end+"','yyyy-mm-dd hh24:mi:ss')" +
                " AND STEP_ID IN('A7700','A7600','A1353','A2353','A3353','A4353','A5353')" +
                " GROUP BY STEP_ID" +
                " ORDER BY STEP_ID";
        String arrOraCntSqlALL="SELECT " +
                "'ARRAY' AS SHOP," +
                " NULL AS OPE," +
                " COUNT(0) AS oraCnt " +
                " FROM ARRAY_GLASS_T " +
                " WHERE glass_start_time >=to_date('"+start+"','yyyy-mm-dd hh24:mi:ss')" +
                " AND glass_start_time <to_date('"+end+"','yyyy-mm-dd hh24:mi:ss')";
        String cfOraCntSql="SELECT " +
                "'CF' AS SHOP," +
                " STEP_ID AS OPE," +
                " COUNT(STEP_ID) AS oraCnt " +
                " FROM CF_GLASS_T " +
                " WHERE glass_start_time >=to_date('"+start+"','yyyy-mm-dd hh24:mi:ss')" +
                " AND glass_start_time <to_date('"+end+"','yyyy-mm-dd hh24:mi:ss')" +
                " AND STEP_ID IN('C7590','C7563','C759I')" +
                " GROUP BY STEP_ID" +
                " ORDER BY STEP_ID";
        String cfOraCntSqlALL="SELECT " +
                "'CF' AS SHOP," +
                " NULL AS OPE," +
                " COUNT(0) AS oraCnt " +
                " FROM CF_GLASS_T " +
                " WHERE glass_start_time >=to_date('"+start+"','yyyy-mm-dd hh24:mi:ss')" +
                " AND glass_start_time <to_date('"+end+"','yyyy-mm-dd hh24:mi:ss')" ;
        String cell1OraCntSql="SELECT " +
                "'CELL1' AS SHOP," +
                " STEP_ID AS OPE," +
                " COUNT(STEP_ID) AS oraCnt " +
                " FROM cell_component_T " +
                " WHERE component_start_time >=to_date('"+start+"','yyyy-mm-dd hh24:mi:ss')" +
                " AND component_start_time <to_date('"+end+"','yyyy-mm-dd hh24:mi:ss')" +
                " AND STEP_ID IN('L1110','L1112','L2110','L2220','L2112','L1240','L2410','L1260')" +
                " GROUP BY STEP_ID" +
                " ORDER BY STEP_ID";
        String cell2OraCntSql="SELECT " +
                "'CELL2' AS SHOP," +
                " STEP_ID AS OPE," +
                " COUNT(STEP_ID) AS oraCnt " +
                " FROM cell2_chip_T " +
                " WHERE chip_start_time >=to_date('"+start+"','yyyy-mm-dd hh24:mi:ss')" +
                " AND chip_start_time <to_date('"+end+"','yyyy-mm-dd hh24:mi:ss')" +
                " AND STEP_ID IN('4600','4610','7200','7210','4670','466X')" +
                " GROUP BY STEP_ID" +
                " ORDER BY STEP_ID";

        List<EdaDiffDModel> arrOra = qr_ora.query(arrOraCntSql, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));
        List<EdaDiffDModel> cfOra = qr_ora.query(cfOraCntSql, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));
        List<EdaDiffDModel> cell1Ora = qr_ora.query(cell1OraCntSql, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));
        List<EdaDiffDModel> cell2Ora = qr_ora.query(cell2OraCntSql, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));

        List<EdaDiffDModel> arrOraALL = qr_ora.query(arrOraCntSqlALL, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));
        List<EdaDiffDModel> cfOraALL = qr_ora.query(cfOraCntSqlALL, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));


        if(arrGp.size()>0) {
            setCnt(arrGp,arrOra,startTime,endTime,updateTime);
            okCnt++;
            }
        if(cfGp.size()>0) {
            setCnt(cfGp,cfOra,startTime,endTime,updateTime);
            okCnt++;
        }
        if(cell1Gp.size()>0) {
            setCnt(cell1Gp,cell1Ora,startTime,endTime,updateTime);
            okCnt++;
        }
        if(cell2Gp.size()>0) {
            setCnt(cell2Gp,cell2Ora,startTime,endTime,updateTime);
            okCnt++;
        }
        if(arrGpALL.size()>0) {
            setCnt(arrGpALL,arrOraALL,startTime,endTime,updateTime);
            okCnt++;
        }
        if(cfGpALL.size()>0) {
            setCnt(cfGpALL,cfOraALL,startTime,endTime,updateTime);
            okCnt++;
        }

        resMsg[0]=String.valueOf(okCnt);
        resMsg[1]=String.valueOf(errCnt);
        resMsg[2]=errMsg;

        return resMsg;
    }

    private List<EdaDiffD> setCnt(List<EdaDiffDModel> gpList,List<EdaDiffDModel> oraList,Timestamp startTime,Timestamp endTime,Timestamp updateTime){
        List<EdaDiffD> arrList=new ArrayList();
        for (EdaDiffDModel gp : gpList) {
            EdaDiffD arrCnt = new EdaDiffD();
            arrCnt.setShop(gp.getShop());
            arrCnt.setOpe(gp.getOpe());
            arrCnt.setEvtStartTimestamp(startTime);
            arrCnt.setEvtEndTimestamp(endTime);
            arrCnt.setGpCnt(gp.getGpCnt());
            if (oraList.size() > 0) {
                oraList.forEach(ora -> {
                    if(StringUtils.isEmpty(gp.getOpe())){
                        if(StringUtils.isEmpty(ora.getOpe()))arrCnt.setOraCnt(ora.getOraCnt());
                    }
                    else if (gp.getOpe().equals(ora.getOpe())) {
                        arrCnt.setOraCnt(ora.getOraCnt());
                    };
                });
            }
            if(null!=arrCnt.getGpCnt()) {
                if(null!=arrCnt.getOraCnt()) {
                    arrCnt.setDifference(arrCnt.getGpCnt() - arrCnt.getOraCnt());
                }else {
                    arrCnt.setDifference(arrCnt.getGpCnt());
                }
                arrCnt.setUpdateTime(updateTime);
                saveEdaDiff.saveCnt(arrCnt);
                arrList.add(arrCnt);
            }
        }
        return arrList;
    }
}
